package com.css.fxfzypg.modules.model.repository.Impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.model.entity.ResettlementPersonnel;
import com.css.fxfzypg.modules.model.repository.ResettlementPersonnelRepository;
import com.css.fxfzypg.modules.model.vo.ResettlementPersonnelVO;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2022年10月05日  16:10
 */
@Repository
public class ResettlementPersonnelRepositoryImpl implements ResettlementPersonnelRepository {

    @Resource
    private SUserService sUserService;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private SysAreaService areaService;

    @Override
    public Map<String, Object> findPage(ResettlementPersonnelVO vo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from resettlement_personnel ");
        StringBuilder whereSql = new StringBuilder(" where is_valid ='" + YNEnum.Y.toString() + "'");
        String orderSql = " order by is_default desc,province_code";
        StringBuilder countSql = new StringBuilder("select count(*) from resettlement_personnel ");
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        if (!vo.getNation()) {
            whereSql.append(" and (org_name = '系统默认' or province='").append(sUser.getProvinceId()).append("')");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getModelName())) {
            whereSql.append(" and model_name like '%" + vo.getModelName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getProvince())) {
            if ("cd726776c7de41e4a41dcdb3d90b2806".equals(vo.getProvince()) || "225ac763ab714524b87d95140a93a4a5".equals(vo.getProvince()) || "8a3f7d3f78ad456f92f487a979091c7f".equals(vo.getProvince()) || "afcbf07008a34c2e85917cfd34008033".equals(vo.getProvince())) {
                //拿到北京上海天津重庆四省的省id
                String pid = areaService.getParentId(vo.getProvince());
                vo.setProvince(pid);
            }
            whereSql.append(" and province='").append(vo.getProvince()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getCity())) {
            whereSql.append(" and (position('").append(vo.getCity()).append("' in city) > 0 ");
            whereSql.append(" or city='全部')");
        }
        String limitSql = " limit " + pageSize + " offset " + (curPage - 1) * pageSize;
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<ResettlementPersonnel> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(ResettlementPersonnel.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public ResettlementPersonnel getModelById(String id) {
        String sql = "select * from resettlement_personnel where is_valid ='" + YNEnum.Y.toString() + "' and id='" + id + "'";
        List<ResettlementPersonnel> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResettlementPersonnel.class));
        return PlatformObjectUtils.isNotEmpty(query) ? query.get(0) : null;
    }

    @Override
    public ResettlementPersonnel getDefaultModel() {
        String sql = "select * from resettlement_personnel where is_valid ='" + YNEnum.Y.toString() +
                "' and is_default='" + YNEnum.Y.toString() + "' limit 1";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResettlementPersonnel.class));
    }

    @Override
    public List<ResettlementPersonnel> getByDivision(String province, String city, int isNa) {
        String sql = "select * from resettlement_personnel where is_valid ='" + YNEnum.Y.toString() + "' and province='" + province + "'";
        if (PlatformObjectUtils.isNotEmpty(city)) {
            sql = sql + " and city='" + city + "'";
        }
        sql = sql + " order by create_time desc ";
        if (isNa == 0) {
            sql = sql + " limit 1 ";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResettlementPersonnel.class));
    }

    @Override
    public void save(ResettlementPersonnel resettlementPersonnel) {
        jdbcTemplate.update("insert into resettlement_personnel(id,model_name,ratio1,ratio2,ratio3,ratio4,is_default,province,city,create_user_id,create_user_name,create_time,org_id,org_name,is_valid,province_code) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                resettlementPersonnel.getId(), resettlementPersonnel.getModelName(), resettlementPersonnel.getRatio1(), resettlementPersonnel.getRatio2(), resettlementPersonnel.getRatio3(), resettlementPersonnel.getRatio4()
                , resettlementPersonnel.getIsDefault(), resettlementPersonnel.getProvince(),
                resettlementPersonnel.getCity(), resettlementPersonnel.getCreateUserId(), resettlementPersonnel.getCreateUserName(), resettlementPersonnel.getCreateTime(),
                resettlementPersonnel.getOrgId(), resettlementPersonnel.getOrgName(), resettlementPersonnel.getIsValid(), resettlementPersonnel.getProvinceCode());
    }

    @Override
    public void update(ResettlementPersonnel resettlementPersonnel) {
        jdbcTemplate.update("update resettlement_personnel set model_name=?,ratio1=?,ratio2=?,ratio3=?,ratio4=?," +
                        "update_user_id=?,update_time=? where id=?", resettlementPersonnel.getModelName(), resettlementPersonnel.getRatio1(),
                resettlementPersonnel.getRatio2(), resettlementPersonnel.getRatio3(), resettlementPersonnel.getRatio4(),
                resettlementPersonnel.getUpdateUserId(), resettlementPersonnel.getUpdateTime(), resettlementPersonnel.getId());
    }

    @Override
    public void delete(String userId, Date updateTime, String id) {
        jdbcTemplate.update("update resettlement_personnel set is_valid=?,update_user_id=?,update_time=? where id=?", YNEnum.N.toString(), userId, updateTime, id);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from resettlement_personnel where province = '" + province + "' and is_valid='1' ", Integer.class);
    }

    @Override
    public Integer countByYearPrefixLike(String taskYear, String currProvinceId) {
            return jdbcTemplate.queryForObject("SELECT count(*) FROM resettlement_personnel where (TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%' or TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%') and province = '" + currProvinceId + "' and is_valid='1' ", Integer.class);
    }
}
